package com.windfone.dao.impl.jdbc.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.windfone.common.ComposeDateIntArrays;
import com.windfone.dao.OrderDao;
import com.windfone.model.Order;
import com.windfone.model.ReceiveMsg;

public class OrderDaoImpl implements OrderDao {
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
		this.jdbcTemplate = jdbcTemplate;
	}
	public int deleteOrder(int keyValue) {
		return 0;
	}

	String GET_ALL_NUMBER_SQL = " SELECT COUNT(1) FROM  orders  ";
	public int getAllRecorderNumber() {
		final List<Integer> result = new ArrayList<Integer>();
		try {
			jdbcTemplate.query(
					GET_ALL_NUMBER_SQL,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}

	public int updateOrder(Order bean) {
		return 0;
	}
 
	String INSERT_ORDER_SQL = " INSERT INTO orders (" +
			"name,productId,seqTime,status,chainId,orderNumber,goodstype,standard,colorName,orderSeq,memberId,price) " +
			" VALUES (?,?,?,?,?,?, ?,?,?,?,?,?) ";
	public int insertOrder(final Order bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				INSERT_ORDER_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,bean.getName());
						ps.setInt(i++,bean.getProductId());
						
						ps.setLong(i++,bean.getTimeSeq());
						ps.setInt(i++,bean.getStatus());
						ps.setInt(i++,bean.getChainId());
						ps.setInt(i++,bean.getOrderNumber());
						ps.setInt(i++,bean.getGoodstype());
						ps.setString(i++,bean.getStandard());
						if ( bean.getColorName() ==null )
							ps.setString(i++,"");
						else
							ps.setString(i++,bean.getColorName());
						ps.setString(i++,bean.getOrderSeq());
						ps.setInt(i++,bean.getMemberId());
						ps.setDouble(i++,bean.getPrice());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;	
	}

	public List<Order> getOrderById(final int keyValue) {
		return null;
	}

	public List<Order> getAllOrder( final int beginRecord) {
		return null;
	}
	 
	String INSERT_RECEIVEMSG_SQL = " INSERT INTO receivedmsg (" +
	"name,email,telnumber,mobile,zipcode ,detailaddress,usermemo,totalCost,payType,postType,memberId,orderSeq,status) " +
	" VALUES (?,?,?,?,?  ,?,?,?,?,?,  ?,?,?) ";
	public int insertReceiveMsg(final ReceiveMsg bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_RECEIVEMSG_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,bean.getName());
						ps.setString(i++,bean.getEmail());
						ps.setString(i++,bean.getTelnumber());
						ps.setString(i++,bean.getMobile());
						ps.setString(i++,bean.getZipcode());
						ps.setString(i++,bean.getDetailaddress());
						ps.setString(i++,bean.getUserMemo());
						ps.setDouble(i++,bean.getTotalCost());
						ps.setString(i++,bean.getPayType());
						ps.setString(i++,bean.getPostType());
						ps.setInt(i++,bean.getMemberId());
						ps.setString(i++,bean.getOrderSeq());
						ps.setInt(i++,bean.getStatus());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;	
	}
	
	private static String GET_RECEIVEMSG_BY_USER_ID_SQL = "SELECT * FROM receivedmsg WHERE memberid=? ";
	public List<ReceiveMsg> getReceiveMsgByUserId(final int userId) {
		final List<ReceiveMsg> result = new ArrayList<ReceiveMsg>();
		try {
			jdbcTemplate.query(
				GET_RECEIVEMSG_BY_USER_ID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, userId);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							ReceiveMsg to= new ReceiveMsg();
 							to.setId(rs.getInt("id"));
 							to.setCreatetime(rs.getTimestamp("createtime") );
 							to.setOrderSeq( rs.getString("orderSeq"));
 							to.setName( rs.getString("name"));
 							to.setEmail( rs.getString("email"));
 							to.setTelnumber( rs.getString("telnumber"));
 							to.setMobile( rs.getString("mobile"));
 							to.setZipcode( rs.getString("zipcode"));
 							to.setDetailaddress( rs.getString("detailaddress"));
 							to.setUserMemo( rs.getString("userMemo"));
 							to.setCompanyMemo( rs.getString("companyMemo"));
 							to.setMemberid( rs.getString("memberid"));
 							to.setPayType( rs.getString("payType"));
 							to.setPostcost( rs.getDouble("postcost"));
 							to.setTotalCost( rs.getDouble("totalCost"));
 							to.setStatus( rs.getInt("status"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	private static String GET_RECEIVEMSG_BY_USER_ID_LIMIT_SQL = "SELECT * FROM receivedmsg WHERE memberid=? ORDER BY  createtime desc LIMIT  ";
	public List<ReceiveMsg> getReceiveMsgByUserId(final int userId,final int limit) {
		final List<ReceiveMsg> result = new ArrayList<ReceiveMsg>();
		String sql = GET_RECEIVEMSG_BY_USER_ID_LIMIT_SQL + limit;
		try {
			jdbcTemplate.query(
					sql,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, userId);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							ReceiveMsg to= new ReceiveMsg();
							to.setId(rs.getInt("id"));
							to.setCreatetime(rs.getTimestamp("createtime") );
							to.setOrderSeq( rs.getString("orderSeq"));
							to.setName( rs.getString("name"));
							to.setEmail( rs.getString("email"));
							to.setTelnumber( rs.getString("telnumber"));
							to.setMobile( rs.getString("mobile"));
							to.setZipcode( rs.getString("zipcode"));
							to.setDetailaddress( rs.getString("detailaddress"));
							to.setUserMemo( rs.getString("userMemo"));
							to.setCompanyMemo( rs.getString("companyMemo"));
							to.setMemberid( rs.getString("memberid"));
							to.setPayType( rs.getString("payType"));
							to.setPostcost( rs.getDouble("postcost"));
							to.setPostType( rs.getString("postType"));
							to.setTotalCost( rs.getDouble("totalCost"));
							to.setStatus( rs.getInt("status"));
							result.add(to);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
 
	public List<Order> getOrderByUserId(int userId) {
		return null;
	}
	
	private static String GET_ORDER_BY_ORDERSEQ_SQL = "SELECT * FROM orders WHERE  orderSeq=? ";
	public List<Order> getOrderByOrderSeq(final String orderSeq) {
		final List<Order> result = new ArrayList<Order>();
		try {
			jdbcTemplate.query(
				GET_ORDER_BY_ORDERSEQ_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setString(1, orderSeq);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Order to= new Order();
 							to.setId(rs.getInt("id"));
 							to.setName(rs.getString("name"));
 							to.setOrderSeq( rs.getString("orderSeq"));
 							to.setGoodstype(rs.getInt("goodstype"));
 							to.setChainId(rs.getInt("chainId"));
 							to.setPrice( rs.getDouble("price"));
 							to.setOrderNumber( rs.getInt("orderNumber"));
 							to.setColorName(rs.getString("colorName"));
 							to.setStandard(rs.getString("standard"));
 							to.setCreatetime(  rs.getTimestamp("createtime"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	private static String GET_RECEIVEMSG_SQL = "SELECT * FROM receivedmsg WHERE orderSeq=? ";
	public ReceiveMsg getReceiveMsgByOrderId(final String orderSeq) {
		ReceiveMsg  rtv = null;
		final List<ReceiveMsg> result = new ArrayList<ReceiveMsg>();
		try {
			jdbcTemplate.query(
					GET_RECEIVEMSG_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setString(1, orderSeq);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							ReceiveMsg to= new ReceiveMsg();
 							to.setId(rs.getInt("id"));
 							to.setOrderSeq( rs.getString("orderSeq"));
 							to.setCreatetime(rs.getTimestamp("createtime") );
 							to.setName( rs.getString("name"));
 							to.setEmail( rs.getString("email"));
 							to.setTelnumber( rs.getString("telnumber"));
 							to.setMobile( rs.getString("mobile"));
 							to.setZipcode( rs.getString("zipcode"));
 							to.setDetailaddress( rs.getString("detailaddress"));
 							to.setUserMemo( rs.getString("userMemo"));
 							to.setCompanyMemo( rs.getString("companyMemo"));
 							to.setMemberid( rs.getString("memberid"));
 							to.setPayType( rs.getString("payType"));
							to.setPostcost( rs.getDouble("postcost"));
							to.setPostType( rs.getString("postType")) ;							
							to.setTotalCost( rs.getDouble("totalCost")) ;							
 							to.setStatus( rs.getInt("status"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size() >0 )
			rtv = result.get(0);
		return rtv;
	}
	
	private static String GET_ORDER_BY_CONDITION_SQL = "SELECT * FROM order WHERE 1=1 ";
	public List<Order> getOrderByCondition(String whereCondition) {
		final List<Order> result = new ArrayList<Order>();
		String sql =  GET_ORDER_BY_CONDITION_SQL + whereCondition;
		try {
			jdbcTemplate.query(
				sql,
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Order to= new Order();
 							to.setId(rs.getInt("id"));
 							to.setOrderSeq( rs.getString("orderSeq"));
 							to.setGoodstype(rs.getInt("goodstype"));
 							to.setChainId(rs.getInt("chainId"));
 							to.setPrice( rs.getDouble("price"));
 							to.setOrderNumber( rs.getInt("orderNumber"));
 							to.setColorName(rs.getString("colorName"));
 							to.setStandard(rs.getString("standard"));
 							to.setCreatetime( new Date( rs.getTimestamp("createtime").getTime()));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	public ReceiveMsg getReceiveMsgByOrderId(int OrderId) {
		return null;
	}
	private static String GET_RECEIVE_BY_CONDITION_SQL = "SELECT * FROM receivedmsg r WHERE 1=1 ";
	public List<ReceiveMsg> getReceiveByCondition(String whereCondition) {
		final List<ReceiveMsg> result = new ArrayList<ReceiveMsg>();
		String sql =  GET_RECEIVE_BY_CONDITION_SQL + whereCondition;
		try {
			jdbcTemplate.query(
				sql,
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							ReceiveMsg to= new ReceiveMsg();
 							to.setId(rs.getInt("id"));
 							to.setOrderSeq( rs.getString("orderSeq"));
 							to.setCreatetime(rs.getTimestamp("createtime") );
 							to.setName( rs.getString("name"));
 							to.setEmail( rs.getString("email"));
 							to.setTelnumber( rs.getString("telnumber"));
 							to.setMobile( rs.getString("mobile"));
 							to.setZipcode( rs.getString("zipcode"));
 							to.setDetailaddress( rs.getString("detailaddress"));
 							to.setUserMemo( rs.getString("userMemo"));
 							to.setCompanyMemo( rs.getString("companyMemo"));
 							to.setMemberid( rs.getString("memberid"));
 							to.setPayType( rs.getString("payType"));
 							to.setPostcost( rs.getDouble("postcost"));
 							to.setTotalCost( rs.getDouble("totalCost"));
 							to.setStatus( rs.getInt("status"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	String SQL_UPDATE_RECEIVEDMSG=" UPDATE  receivedmsg SET status=?  WHERE orderSeq=?";
	public int updateReceiveMsg(final String orderSeq,final int status) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					SQL_UPDATE_RECEIVEDMSG,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,status);
						ps.setString(i++,orderSeq);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	String SQL_UPDATE_RECEIVEDMSG_BEAN=" UPDATE  receivedmsg SET status=?,companyMemo=?  WHERE orderSeq=?";
	public int updateReceiveMsg(final ReceiveMsg bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					SQL_UPDATE_RECEIVEDMSG_BEAN,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setInt(i++,bean.getStatus());
							ps.setString(i++,bean.getCompanyMemo());
							ps.setString(i++,bean.getOrderSeq());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	String SQL_UPDATE_RECEIVEDMSG_BEAN_BYUSER=" UPDATE  receivedmsg SET name=?,telnumber=?, mobile=?,zipcode=?, detailaddress=?,userMemo=? WHERE orderSeq=?";
	public int updateReceiveMsgByUser(final ReceiveMsg bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					SQL_UPDATE_RECEIVEDMSG_BEAN_BYUSER,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setString(i++,bean.getName());
							ps.setString(i++,bean.getTelnumber());
							ps.setString(i++,bean.getMobile());
							ps.setString(i++,bean.getZipcode());
							ps.setString(i++,bean.getDetailaddress());
							ps.setString(i++,bean.getUserMemo());
							ps.setString(i++,bean.getOrderSeq());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	public List<ReceiveMsg> getReceiveMsgByUserIdAndCondition(final int id,final String whereStr) {
		final List<ReceiveMsg> result = new ArrayList<ReceiveMsg>();
		String sql =  GET_RECEIVE_BY_CONDITION_SQL + " AND memberid="+id + whereStr;
		try {
			jdbcTemplate.query(
				sql,
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							ReceiveMsg to= new ReceiveMsg();
 							to.setId(rs.getInt("id"));
 							to.setOrderSeq( rs.getString("orderSeq"));
 							to.setCreatetime(rs.getTimestamp("createtime") );
 							to.setName( rs.getString("name"));
 							to.setEmail( rs.getString("email"));
 							to.setTelnumber( rs.getString("telnumber"));
 							to.setMobile( rs.getString("mobile"));
 							to.setZipcode( rs.getString("zipcode"));
 							to.setDetailaddress( rs.getString("detailaddress"));
 							to.setUserMemo( rs.getString("userMemo"));
 							to.setCompanyMemo( rs.getString("companyMemo"));
 							to.setMemberid( rs.getString("memberid"));
 							to.setPayType( rs.getString("payType"));
 							to.setPostcost( rs.getDouble("postcost"));
 							to.setTotalCost( rs.getDouble("totalCost"));
 							to.setStatus( rs.getInt("status"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	
//	String DELETE_SQL = " DELETE FROM  fitting WHERE id=? ";
//	public int deleteFitting(final int keyValue) {
//		int rtv = 0;
//		try {
//			rtv = jdbcTemplate.update(
//					DELETE_SQL,
//				new PreparedStatementSetter() {
//					public void setValues(PreparedStatement ps)	throws SQLException {
//						ps.setInt(1, keyValue);
//					}
//				}
//			);
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//		return rtv;
//	}
	
	String DELETE_RECEIVE_SQL = " DELETE FROM  receivedmsg WHERE orderSeq=? ";
	String DELETE_ORDERS_SQL = " DELETE FROM  orders WHERE orderSeq=? ";
	public int deleteOrderByOrderSeq(final String orderSeq) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				DELETE_ORDERS_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setString(1, orderSeq);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	public int delteReceiveMsg(final String orderSeq) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				DELETE_RECEIVE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setString(1, orderSeq);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	 
	String DELETE_RECEIVE_MEMBER_ID_SQL = " DELETE FROM  receivedmsg WHERE orderSeq=? AND memberid=?";
	String DELETE_ORDERS_MEMBER_SQL = " DELETE FROM  orders WHERE orderSeq=? AND memberid=? ";
	public int deleteOrderByOrderSeqMemberId(final String orderSeq,final int memberId) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_ORDERS_MEMBER_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setString(1, orderSeq);
							ps.setInt(2, memberId);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	public int delteReceiveMsgByOrderSeqAndMemberId(final String orderSeq,final int memberId) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_RECEIVE_MEMBER_ID_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setString(1, orderSeq);
							ps.setInt(2, memberId);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	public List<ReceiveMsg> getAllOrderHistory(int id) {
		final List<ReceiveMsg> result = new ArrayList<ReceiveMsg>();
		String sql =  "  SELECT * FROM orderhistory r WHERE 1=1 " + " AND userId="+id ;
		try {
			jdbcTemplate.query(
				sql,
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							ReceiveMsg to= new ReceiveMsg();
							to.setHistoryType("0");
 							to.setId(rs.getInt("id"));
// 							to.setOrderSeq( rs.getString("orderSeq"));
 							String dateStr = rs.getString("buydate");
 							if ( dateStr !=null && !dateStr.equals("")){
 								to.setCreatetime( ComposeDateIntArrays.changeStrToDate( ComposeDateIntArrays.splitDateStr(dateStr)) );
 							}
 							to.setName( rs.getString("productName"));
// 							to.setEmail( rs.getString("email"));
// 							to.setTelnumber( rs.getString("telnumber"));
// 							to.setMobile( rs.getString("mobile"));
// 							to.setZipcode( rs.getString("zipcode"));
// 							to.setDetailaddress( rs.getString("detailaddress"));
// 							to.setUserMemo( rs.getString("userMemo"));
// 							to.setCompanyMemo( rs.getString("companyMemo"));
 							to.setMemberid( rs.getString("userId"));
// 							to.setPayType( rs.getString("payType"));
// 							to.setPostcost( rs.getDouble("postcost"));
 							String priceStr = rs.getString("price");
 							if ( priceStr !=null )
 							to.setTotalCost(  rs.getDouble("price") );
// 							to.setStatus( rs.getInt("status"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	

}
